package com.wframe.project.former.dataservices.marketing;

import com.frame.database.JBaseDao;
import com.frame.database.JdataBean;
import com.frame.database.PageResult;
import com.frame.util.DateConvert;
import com.thoughtworks.xstream.converters.basic.DateConverter;
import com.wframe.project.former.dataservices.products.dao.ProductClassDao;
import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2017/6/14.
 */
public class FillReductionDao extends JBaseDao {
    public FillReductionDao() {
        super("wb_promotion_money_off");
    }

    public PageResult queryFillReduction(JdataBean parms, int size, int page) throws Exception {
        String sql = "select p.id promotid,p.`name` promotname,p.sort_value,p.platform,p.state,p.start_time,p.end_time,p.product_type,case product_type WHEN '0' THEN '全部商品' ELSE '部分商品' END manner,(select c.name from wb_products_class c where p.promotion_type=2 and product_type=3 and p.product_val=c.id) classname,UNIX_TIMESTAMP(p.start_time) startmsec,UNIX_TIMESTAMP(p.end_time) endmsec,UNIX_TIMESTAMP() newtime from wb_promotions p where  promotion_type=2 and (state=0 or state=1)";
        List parmlist = new ArrayList();
        if (parms != null) {
            if (StringUtils.isNotBlank(parms.getString("name"))) {
                sql += " and name like ?";
                parmlist.add("%" + parms.getString("name") + "%");
            }
            if (StringUtils.isNotBlank(parms.getString("platform"))) {
                sql += " and platform=?";
                parmlist.add(parms.getString("platform"));
            }
            if (StringUtils.isNotBlank(parms.getString("start"))) {
                sql += " and start_time>=?";
                parmlist.add(DateConvert.toDate(parms.getString("start") + " 00:00:00"));
            }
            if (StringUtils.isNotBlank(parms.getString("end"))) {
                sql += " and start_time<=?";
                parmlist.add(DateConvert.toDate(parms.getString("end") + " 23:59:59"));
            }

            //已暂停
            if (StringUtils.isNotBlank(parms.getString("state")) && parms.getString("state").equals("0")) {
                sql += " and state=0 and start_time<now() and end_time>NOW()";
            }
            //未开始
            if (StringUtils.isNotBlank(parms.getString("state")) && parms.getString("state").equals("1")) {
                sql += " and state=1 and start_time>now()";
            }
            //进行中
            if (StringUtils.isNotBlank(parms.getString("state")) && parms.getString("state").equals("2")) {
                sql += " and state =1 AND start_time<now() and end_time>NOW() ";
            }
            //已失效
            if (StringUtils.isNotBlank(parms.getString("state")) && parms.getString("state").equals("3")) {
                sql += " and (state =1 or state=0) and  end_time<NOW()";
            }
        }
        sql += " ORDER BY sort_value desc,id desc";
        return getSession().query2(sql, parmlist, size, page);
    }

    public List<JdataBean> moneyoffById(String id) throws Exception {
        return getSession().query("select f.id,f.off_money,f.discount_money,f.discount_rate,t.platform,t.`name` promotname,t.time,t.state,t.start_time,t.id promotid,t.end_time,t.sort_value from wb_promotions t,wb_promotion_money_off f where t.id=f.promotionid and f.promotionid=? and NOT ISNULL(discount_money)", new Object[]{id});
    }

    public List<JdataBean> moneyoffByIdRata(String id) throws Exception {
        return getSession().query("select f.id,f.off_money,f.discount_money,f.discount_rate,t.platform,t.`name` promotname,t.time,t.state,t.start_time,t.id promotid,t.end_time from wb_promotions t,wb_promotion_money_off f where t.id=f.promotionid and f.promotionid=? and NOT ISNULL(discount_rate)", new Object[]{id});
    }

    public JdataBean promotByid(String id) throws Exception {
        return getSession().getObject("SELECT * from wb_promotions WHERE id=?", new Object[]{id});
    }

    public void delectMontyOff(String id) throws HibernateException, Exception {
        getSession().delete("delete from wb_promotion_money_off where promotionid=?", new Object[]{id});
    }

    public void addMoneyOff(Map date) throws Exception {
        getSession().save("wb_promotion_money_off", date);
    }

    public PageResult queryMoneyProduct(JdataBean parms, int size, int pageno) throws Exception {
        String sql = "SELECT m.id,m.`name` pname,m.`code`,(select ifnull(pr.amount,0)-ifnull(pr.djamount,0) from  wb_products_prices pr where pr.productid=m.id) productamount from  wb_products m where m.`status`=2";
        List parmlist = new ArrayList();
        String[] codelist = null;
        String code = "";
        if (parms != null) {
            if (StringUtils.isNotBlank(parms.getString("pname"))) {
                sql += " and m.name like ?";
                parmlist.add("%" + parms.getString("pname") + "%");
            }
            if (StringUtils.isNotBlank(parms.getString("code"))) {
                String codestr = parms.getString("code");
                sql += " and m.code in (" + codestr + ") ";
            }
            if (StringUtils.isNotBlank(parms.getString("classid"))) {
                if ("null".equals(parms.getString("classid"))) {
                    sql += " and m.id not in (select productid from wb_products_and_class)";
                } else {
                    JdataBean clsbean = new ProductClassDao().getBean(parms.getString("classid"));
                    sql += " and m.id in (select productid from wb_products_and_class where classcode like ? or classcode=?)";
                    parmlist.add(clsbean.getString("code") + ".%");
                    parmlist.add(clsbean.getString("code"));
                }
            }
        }
        sql += " group by m.id desc";
        return getSession().query2(sql, parmlist, size, pageno);
    }

    public List<JdataBean> canCreate() throws Exception {
        return getSession().query("SELECT * from wb_promotions where (promotion_type=2 or promotion_type = 3)  and product_type is not null and end_time > now()", null);
    }

    public List<JdataBean> canCreate(String proClass) throws Exception {
        String sql = "SELECT id FROM wb_promotions where (promotion_type = 2 or promotion_type = 3) and end_time> now() " +
                "and (product_type=0 " +
                "or (product_type=3 and (product_val=? or product_val=1 or 1 = ? )) " +
                "or (product_type=2 and product_val in(SELECT wpp.promotionid from wb_promotion_product wpp ,wb_products_and_class wac where " +
                "wac.productid = wpp.productid and wac.classid=?)) " +
                "or (product_type=1 and product_val in (SELECT productid from wb_products_and_class where classid=?))" +
                ")";
        return getSession().query(sql, new Object[]{proClass, proClass, proClass,proClass});
    }


    public List<JdataBean> canCreate(List<String> ids) throws Exception {
        ArrayList<Object> list = new ArrayList<>();
        for (int i = 0; i < 3; i++) {
            for (int j = 0; j < ids.size(); j++) {
                list.add(ids.get(j));
            }

        }
        String inIds = "";
        for (int i = 0; i < ids.size(); i++) {
            if (i == ids.size() - 1) {
                inIds += "?";
                break;
            }
            inIds += "?,";
        }
        String sql = "SELECT id FROM wb_promotions where promotion_type = 2 and end_time > now() and (state =0 or state =1) " +
                "and (product_type=0 or " +
                "(product_type=1 and product_val in (" + inIds + " ) ) " +
                "or (product_type=2 and product_val in (SELECT promotionid FROM wb_promotion_product where productid in (" + inIds + "))) " +
                "or (product_type=3 and product_val in (SELECT id FROM wb_products_and_class WHERE productid in (" + inIds + "))) " +
                ")";
        return getSession().query(sql, list.toArray(new String[list.size()]));
    }

    public void addPromotionProduct(JdataBean date) throws Exception {
        getSession().save("wb_promotion_product", date.getDataMap());
    }

    public void delectPromotionProducr(String promotionid) throws HibernateException, Exception {
        getSession().delete("delete from wb_promotion_product where promotionid=?", new Object[]{promotionid});
    }

    //二级目录下的单个商品
    public List<JdataBean> queryProductToclass(String classid) throws Exception {
        return getSession().query("SELECT id FROM wb_promotions where (promotion_type = 3 or promotion_type = 2) and end_time > now() and (product_type=0 " +
                "or (product_type=3 and (product_val = ? or product_val in (select id from wb_products_class where pid = ?))) " +
                "or (product_type=2 and product_val in(SELECT promotionid FROM wb_promotion_product WHERE productid IN (SELECT productid FROM wb_products_and_class WHERE classid in (select id from wb_products_class where pid = ?)))) " +
                "or (product_type=1 and product_val in (SELECT productid FROM wb_products_and_class WHERE classid in (select id from wb_products_class where pid = ?))))", new Object[]{classid,classid,classid,classid});
    }

}
